<?php

/**
 * Class Common_tools
 */
class Common_excelexport extends CI_Model
{
    /**
     * Common_tools constructor.
     */
    public function __construct()
    {
        parent::__construct();
        $this->load->database();
    }

    /** AJAX 返回 跟踪信息,直接返回了
     * @param string $message
     * @param bool $ok
     */
    public function _JSONRESULT($message = '错误信息', $ok = false)
    {
        $result = array();
        $result["success"] = $ok;
        $result["msg"] = print_r($message, true);
        $result["obj"] = "";
        echo json_encode($result);
        exit;
    }

    public function MonthExport($template)
    {
        $objActiveSheet = self::createExcelByTemplate($template);
        $startdate = Date('Y-m-1');
        $enddate = Date('Y-m-1', strtotime("+1 month"));
        $objActiveSheet->setCellValue("A1", Date("Y年m月", strtotime($startdate)) . "网络小组 IP地址月报");
        // 1.本月新增/删除IP段
        $dd = $this->db->query("SELECT COUNT(*) as NUMS FROM nd_network 
          WHERE createdatetime >='" . $startdate . "' AND createdatetime < '" . $enddate . "' AND IS_DISABLED = 0")->row_array();
        $params['network_add'] = $dd['NUMS'];
        $dd = $this->db->query("SELECT COUNT(*) as NUMS FROM nd_network 
          WHERE modifydatetime >='" . $startdate . "' AND modifydatetime < '" . $enddate . "' AND IS_DISABLED = 1")->row_array();
        $params['network_del'] = $dd['NUMS'];
        $objActiveSheet->setCellValue("B2", "新增" . $params['network_add'] . "个，删除" . $params['network_del'] . "个");
        $dd = $this->db->query("SELECT * FROM nd_network
          WHERE createdatetime >='" . $startdate . "' AND createdatetime < '" . $enddate . "' AND IS_DISABLED = 0")->result_array();
        $iRow = 4;
        $iStart = $iRow;
        foreach ($dd as $item) {
            if ($iRow > $iStart) {
                $objActiveSheet->insertNewRowBefore($iRow);
            }
            $objActiveSheet->setCellValue("B" . $iRow, $item['NETWORK'] . '/' . $item['MASK'] . '-' . $item['NAME']);
            $objActiveSheet->setCellValue("C" . $iRow, $item['MEMO']);
            $iRow ++;
        }
        if ($iRow > ($iStart+1)){
            $objActiveSheet->mergeCells("A".$iStart.":A".($iRow-1));
        }
        $dd = $this->db->query("SELECT * FROM nd_network 
          WHERE modifydatetime >='" . $startdate . "' AND modifydatetime < '" . $enddate . "' AND IS_DISABLED = 1")->result_array();
        if ($iRow == $iStart) $iRow++;
        $iStart = $iRow;
        foreach ($dd as $item) {
            if ($iRow > $iStart) {
                $objActiveSheet->insertNewRowBefore($iRow);
            }
            $objActiveSheet->setCellValue("B" . $iRow, $item['NETWORK'] . '/' . $item['MASK'] . '-' . $item['NAME']);
            $objActiveSheet->setCellValue("C" . $iRow, $item['NETWORK'] . '/' . $item['MASK'] . '-' . $item['MEMO']);
            $iRow ++;
        }
        if ($iRow >  ($iStart+1)){
            $objActiveSheet->mergeCells("A".$iStart.":A".($iRow-1));
        }
        //  2.本月占用/释放IP数
        if ($iRow == $iStart) $iRow++;
        $dd = $this->db->query("SELECT COUNT(*) as NUMS FROM nd_ip 
          WHERE IS_DISABLED=0 AND STATUS <> '未登录' AND modifydatetime >='".$startdate."' AND modifydatetime < '" . $enddate . "'")->row_array();
        $objActiveSheet->setCellValue("B".($iRow+1),$dd["NUMS"].'个');
        $dd = $this->db->query("SELECT COUNT(*) as NUMS FROM nd_ip 
          WHERE IS_DISABLED=0 AND STATUS = '未登录' AND modifydatetime >='".$startdate."' AND modifydatetime < '" . $enddate . "'")->row_array();
        $objActiveSheet->setCellValue("B".($iRow+2),$dd["NUMS"].'个');
        // 3.本月账号占用数
        $iRow += 5;
        $iStart = $iRow;
        $dd = $this->db->query("SELECT MAN_USE,COUNT(*) as NUMS FROM nd_ip 
          WHERE IS_DISABLED=0 AND STATUS <> '未登录' AND modifydatetime >='".$startdate."' AND modifydatetime < '" . $enddate . "' AND IS_DISABLED = 0
            GROUP BY MAN_USE")->result_array();
        foreach($dd as $item){
            if ($iRow > $iStart){
                $objActiveSheet->insertNewRowBefore($iRow);
            }
            $objActiveSheet->setCellValue("A".$iRow,$item['MAN_USE']);
            $objActiveSheet->setCellValue("B".$iRow,$item['NUMS']);
            $iRow ++;
        }
        // 4.资源紧张网段(占用率达80%以上)
        if ($iRow == $iStart) $iRow ++;
        $sql = "SELECT NDN.* , ROUND((B.USE1+B.USE2)/(B.USE1+B.USE2+B.NOTUSE)) as RATE
                    FROM  (
                        SELECT
                                 ID_NETWORK,
                                 SUM(IF(STATUS='临时登录',NUMS,0)) AS USE1,
                                 SUM(IF(STATUS='已登录',NUMS,0)) AS USE2,
                                 SUM(IF(STATUS='未登录',NUMS,0)) AS NOTUSE
                         FROM (
                                 SELECT ID_NETWORK,STATUS,COUNT(*) AS NUMS
                                 FROM nd_ip WHERE IS_DISABLED = 0
                                 GROUP BY ID_NETWORK,STATUS
                         ) AS A
                         GROUP BY ID_NETWORK
                    ) B,nd_network NDN WHERE B.ID_NETWORK = NDN.ID 
                        AND ((B.USE1+B.USE2)/(B.USE1+B.USE2+B.NOTUSE)> 0.8)";
        $dd = $this->db->query($sql)->result_array();
        $iRow += 2;
        $iStart = $iRow;
        foreach($dd as $item){
            if ($iRow > $iStart){
                $objActiveSheet->insertNewRowBefore($iRow);
            }
            $objActiveSheet->setCellValue("A".$iRow,$item['NETWORK']);
            $objActiveSheet->setCellValue("B".$iRow,$item['RATE']);
            $iRow ++;
        }
        // 5.账号新增/删除
        if ($iRow == $iStart) $iRow ++;
        $dd = $this->db->query("SELECT COUNT(*) as NUMS FROM sys_log 
          WHERE `desc` like '%新增记录%' AND `table` = 'sys_user' AND logTime >='".$startdate."' AND logTime < '" . $enddate . "'")->row_array();
        $objActiveSheet->setCellValue("B".($iRow+1),$dd["NUMS"].'个');
        $dd = $this->db->query("SELECT COUNT(*) as NUMS FROM sys_log 
          WHERE `desc` like '%删除记录%' AND `table` = 'sys_user' AND logTime >='".$startdate."' AND logTime < '" . $enddate . "'")->row_array();
        $objActiveSheet->setCellValue("B".($iRow+2),$dd["NUMS"].'个');
        // 6.项目完工时间已到但存残留项
        $iRow += 5;
        $iStart = $iRow;
        $sql = "SELECT MAN_USE,COUNT(*) as NUMS FROM nd_ip WHERE DATE_END < '".$enddate."' GROUP BY MAN_USE";
        $dd = $this->db->query($sql)->result_array();
        foreach ($dd as $item){
            if ($iRow > $iStart){
                $objActiveSheet->insertNewRowBefore($iRow);
            }
            $objActiveSheet->setCellValue("A".$iRow,$item['MAN_USE']);
            $objActiveSheet->setCellValue("B".$iRow,$item['NUMS']);
            $iRow ++;
        }
        return self::saveExcelWithTemplate($template);
    }

    private function createExcelByTemplate($template)
    {
        require_once(APPPATH . 'libraries/PHPExcel1.7.6/PHPExcel.php');
        $fn = FCPATH . "excel/Template/" . $template . ".xlsx";
        $tpl = PHPExcel_IOFactory::load($fn);
        $this->target = clone $tpl;
        $objActiveSheet = $this->target->getActiveSheet();
        return $objActiveSheet;
    }

    private function saveExcelWithTemplate($prefix)
    {
        $objWriter = PHPExcel_IOFactory::createWriter($this->target, 'Excel2007');
        $data['fileName'] = $prefix . '_' . date('Ymd') . '.xlsx';
        $path = FCPATH . '/excel/';
        $data['urlFile'] = base_url() . '/excel/' . $data['fileName'];
        if (!is_dir($path)) {
            mkdir($path);
            chmod($path, 0777);
        }
        $objWriter->save($path . $data['fileName']);
        return $data;
    }

}
